# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import pymysql
import redis
from itemadapter import ItemAdapter
from scrapy.exceptions import DropItem


class RedisPipeline:
    def open_spider(self, spider):
        self.r = redis.Redis(host='127.0.0.1')

    def close_spider(self, spider):
        self.r.close()

    def process_item(self, item, spider):
        if self.r.sadd(spider.name, item['name']):
            return item
        raise DropItem()


class MysqlPipeline:
    def open_spider(self, spider):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            port=3306,
            db='qianmu_gp01',
            user='root',
            passwd='root',
            charset='utf8'
        )
        self.cursor = self.conn.cursor()

    def close_spider(self, spider):
        self.cursor.close()
        self.conn.close()

    def process_item(self, item, spider):
        keys, values = zip(*item.items())
        sql = "insert into `{}`({})values({})".format(
            'universities',
            ','.join(keys),
            ','.join(['%s'] * len(keys))
        )
        # sql = "insert into universities(`name`, `rank`, `country`, `state`, `city`, `undergraduate_count`,`postgraduate_count`," \
        #       "`website`)values('%s', '%s', '%s', '%s', ,'%s', %s, %s, '%s') "
        # values = list(item.values())
        self.cursor.execute(sql, values)
        self.conn.commit()
        return item
